USE CHINADW;
GO
-- 01. 创建接收DBM数据库中
--  <ms009_actmgmt.act_dlaunch_access_record> 
-- 的接收表
CREATE TABLE ChinaDW.ODS.DBM_ACT_DLAUNCH_ACCESS_RECORD
(
	id varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	page_access_id varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	activity_id varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	activity_name varchar(256) COLLATE Chinese_PRC_CI_AS NULL,
	user_id varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	user_name varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	login_data date NULL,
	login_time char(10) NULL,
	page_code varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	h5_access_id varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	product_id varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	product_name varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	create_time datetime NULL,
	logout_data date NULL,
	logout_time time NULL,
	ETL_Time datetime DEFAULT getdate() NULL
);
GO
-- 02. 创建接收DBM数据库中
--  <ms009_actmgmt.act_dlaunch_answer_record> 
-- 的接收表
CREATE TABLE ChinaDW.ODS.DBM_ACT_DLAUNCH_ANSWER_RECORD
(
	id varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	h5_access_id varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	activity_id varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	activity_name varchar(256) COLLATE Chinese_PRC_CI_AS NULL,
	user_id varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	user_name varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	question_category int NULL,
	question_type int NULL,
	question_code varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	answer_record varchar(1024) COLLATE Chinese_PRC_CI_AS NULL,
	product_id varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	product_name varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	create_time datetime NULL,
	ETL_Time datetime DEFAULT getdate() NULL
);
GO
-- 03. 创建接收DBM数据库中
--  <ms009_actmgmt.act_dlaunch_page_record> 
-- 的接收表
CREATE TABLE ChinaDW.ODS.DBM_ACT_DLAUNCH_PAGE_RECORD
(
	id varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	activity_id varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	activity_name varchar(256) COLLATE Chinese_PRC_CI_AS NULL,
	user_id varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	user_name varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	page_code varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	status int NULL,
	h5_access_id varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	product_id varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	product_name varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	create_time datetime NULL,
	ETL_Time datetime DEFAULT getdate() NULL
);
GO
-- 04. 创建接收DBM数据库中
--  <ms009_actmgmt.act_dlaunch_video_record> 
-- 的接收表
CREATE TABLE ChinaDW.ODS.DBM_ACT_DLAUNCH_VIDEO_RECORD
(
	id varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	page_access_id varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	activity_id varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	activity_name varchar(256) COLLATE Chinese_PRC_CI_AS NULL,
	user_id varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	user_name varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	video_code varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	view_length varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	h5_access_id varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	product_id varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	product_name varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	create_time datetime NULL,
	ETL_Time datetime DEFAULT getdate() NULL
);
GO
-- 05.创建接收RPM手工表
--  < ODS_02_DBM_Maunal_Input_Data.SQL > 
CREATE TABLE ODS.EXCEL_DLAUNCH_RPM_MANUAL_INPUT
(
	ActivityID VARCHAR(64),
	ActivityName VARCHAR(256) COLLATE Chinese_PRC_CI_AS NULL,
	UserId VARCHAR(64),
	UserName VARCHAR(128) COLLATE Chinese_PRC_CI_AS NULL,
	H5AccessId VARCHAR(64),
	PageCode VARCHAR(50),
	Answer VARCHAR(255),
	DataType VARCHAR(20),
	TimeSpand INT,
	LoginDate DATETIME,
	CreateDate DATETIME,
	ETL_Time datetime DEFAULT getdate() NULL
);
GO
-- 06.创建EXCEL手工维护表
--  < ODS_02_DBM_Maunal_Input_Data.SQL > 
CREATE TABLE ChinaDW.ODS.EXCEL_DLAUNCH_LIST
(
	ActivityName varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	ActivityID varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	ActivityType int NULL,
	ActivityStatus int NULL,
	ActivityTTLUnquiePges int NULL,
	DataSource VARCHAR(50) DEFAULT 'DBM' NULL,
	StartTime datetime NULL,
	ProductID varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	ProductName varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	ETL_Time datetime DEFAULT getdate() NULL
);
GO
CREATE TABLE ChinaDW.ODS.EXCEL_DLAUNCH_PAGE_CODE_FILTER
(
	ActivityID varchar(128) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	DataType varchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL,
	DataSource varchar(50) COLLATE Chinese_PRC_CI_AS DEFAULT 'DBM' NULL,
	PageCode VARCHAR(50) NOT NULL,
	TAG tinyint DEFAULT 1 NULL
);
-- 20211221 to take page code in varchar type
--！ 数据涉及敏感信息，GIT上已被移除，但组件保留
ALTER TABLE ChinaDW.ODS.EXCEL_DLAUNCH_PAGE_CODE_FILTER ALTER COLUMN PageCode VARCHAR(50);
GO
TRUNCATE TABLE ODS.EXCEL_DLAUNCH_RPM_MANUAL_INPUT;
GO
INSERT INTO ODS.EXCEL_DLAUNCH_RPM_MANUAL_INPUT
	(ActivityID, ActivityName, UserId, UserName, H5AccessId, PageCode, Answer, DataType, TimeSpand, LoginDate, CreateDate)
VALUES('test59207', 'test', '9886782e03e46342081517b498e08243', 'testUser', 'testUser990996b8', '1', null, 'ACCESS', '16', '2020/8/6 18:22', '2020/8/6 18:22')
;
	
GO
INSERT INTO ODS.EXCEL_DLAUNCH_RPM_MANUAL_INPUT
	(ActivityID, ActivityName, UserId, UserName, H5AccessId, PageCode, Answer, DataType, TimeSpand, LoginDate, CreateDate)
VALUES('test59207', 'test', '095df3f59220605cc60030c26d42979c', 'testUser1', '0x22eadc47061031ec9d136957bc13b', '1', null, 'ACCESS', '42', '2020/9/21 16:09', '2020/9/21 16:09'),
	('test59207', 'test', '095df3f59220605cc60030c26d42979c', 'testUser1', '0x5c5310fcddc0cb60da5748a28aba0', '1', null, 'ACCESS', '0', '2020/9/21 16:10', '2020/9/21 16:10'),
	('test59207', 'test', '095df3f59220605cc60030c26d42979c', 'testUser1', '0x1cfbdde21d60fc48bb6c242917500', '1', null, 'ACCESS', '0', '2020/9/21 16:10', '2020/9/21 16:10'),
	('test59207', 'test', '56a4720be70b6c4f14625effa380349f', 'testUser2', '0x49e69389b12f0298c506e3fab0bcb', '1', null, 'ACCESS', '22', '2020/9/22 12:42', '2020/9/22 12:42'),
	('test59207', 'test', '56a4720be70b6c4f14625effa380349f', 'testUser2', '0x6a03b16c08a170be07ccb696684e8', '1', null, 'ACCESS', '7', '2020/9/22 12:42', '2020/9/22 12:42'),
	('test59207', 'test', '56a4720be70b6c4f14625effa380349f', 'testUser2', '0x8e9c1dc573938eb5ccb2f87e6ccc6', '1', null, 'ACCESS', '12', '2020/10/14 10:52', '2020/10/14 10:52'),
	('test59207', 'test', '56a4720be70b6c4f14625effa380349f', 'testUser2', '0xffebd6e51f3865f21ce8dc27d486b', '1', null, 'ACCESS', '236', '2020/11/25 18:07', '2020/11/25 18:07'),
	('test59207', 'test', '56a4720be70b6c4f14625effa380349f', 'testUser2', '0xf853ac649f8c29576b16273637855', '1', null, 'ACCESS', '4', '2020/11/25 18:11', '2020/11/25 18:11'),
	('test59207', 'test', '56a4720be70b6c4f14625effa380349f', 'testUser2', '0x75fe05dde71453a1c419c34ef4a44', '1', null, 'ACCESS', '568', '2020/11/25 18:12', '2020/11/25 18:12'),
	('test59207', 'test', '56a4720be70b6c4f14625effa380349f', 'testUser2', '0x1fec466539411cb82ce4eff57fb76', '1', null, 'ACCESS', '44', '2020/11/25 19:43', '2020/11/25 19:43'),
	('test59207', 'test', '56a4720be70b6c4f14625effa380349f', 'testUser2', '0xe1fcf9be8bd1980ca3ac056925bda', '1', null, 'ACCESS', '1608', '2020/11/25 19:44', '2020/11/25 19:44'),
	('test59207', 'test', '56a4720be70b6c4f14625effa380349f', 'testUser2', '0xc2062674002f39764026d1a891723', '1', null, 'ACCESS', '736', '2020/11/26 17:05', '2020/11/26 17:05'),
	('test59207', 'test', '56a4720be70b6c4f14625effa380349f', 'testUser2', '0x222d6ff6c98364fb9523c07836cf4', '1', null, 'ACCESS', '699', '2020/11/26 17:17', '2020/11/26 17:17'),
	('test59207', 'test', '56a4720be70b6c4f14625effa380349f', 'testUser2', '0x51df09c06f15a777a4b50b4957f6d', '1', null, 'ACCESS', '16', '2020/11/26 17:39', '2020/11/26 17:39'),
	('test59207', 'test', '56a4720be70b6c4f14625effa380349f', 'testUser2', '0x5f6a4bab5a30939dad920525a359f', '1', null, 'ACCESS', '324', '2020/11/26 17:40', '2020/11/26 17:40'),
	('test59207', 'test', '56a4720be70b6c4f14625effa380349f', 'testUser2', '0x44cff02b8ecccc971f4d3c428a1d0', '1', null, 'ACCESS', '76', '2020/11/26 18:16', '2020/11/26 18:16'),
	('test59207', 'test', '56a4720be70b6c4f14625effa380349f', 'testUser2', '0xdd19a00e9bb7dac7fc52de88ec703', '1', null, 'ACCESS', '284', '2020/11/26 18:17', '2020/11/26 18:17'),
	('test59207', 'test', '56a4720be70b6c4f14625effa380349f', 'testUser2', '0xc20690c00f27525dad624d93b92bb', '1', null, 'ACCESS', '0', '2020/11/26 18:22', '2020/11/26 18:22'),
	('test59207', 'test', '56a4720be70b6c4f14625effa380349f', 'testUser2', '0xb7595490cb988fd808c681aeb3be4', '1', null, 'ACCESS', '4', '2020/11/26 20:47', '2020/11/26 20:47'),
	('test59207', 'test', '56a4720be70b6c4f14625effa380349f', 'testUser2', '0x618ea7170e1a512fb7775eda4af26', '1', null, 'ACCESS', '156', '2020/11/26 20:47', '2020/11/26 20:47'),
	('test59207', 'test', '56a4720be70b6c4f14625effa380349f', 'testUser2', '0x195c8867bf017b223671f1d620076', '1', null, 'ACCESS', '104', '2020/11/27 9:54', '2020/11/27 9:54'),
	('test59207', 'test', '56a4720be70b6c4f14625effa380349f', 'testUser2', '0xa65543b694e3a88c32c0c4675f05d', '1', null, 'ACCESS', '2628', '2020/11/27 9:56', '2020/11/27 9:56'),
	('test59207', 'test', '56a4720be70b6c4f14625effa380349f', 'testUser2', '0x6e02907b21c95bb5e6c9d78452acc', '1', null, 'ACCESS', '574', '2020/11/27 12:35', '2020/11/27 12:35'),
	('test59207', 'test', '56a4720be70b6c4f14625effa380349f', 'testUser2', '0xd46c7ec59cbe99c5b3ac316de97d4', '1', null, 'ACCESS', '630', '2020/11/27 12:48', '2020/11/27 12:48'),
	('test59207', 'test', '56a4720be70b6c4f14625effa380349f', 'testUser2', '0xe7b1b93e1fb4eea0f780b3ac04bfe', '1', null, 'ACCESS', '4', '2020/11/27 12:58', '2020/11/27 12:58'),
	('test59207', 'test', '56a4720be70b6c4f14625effa380349f', 'testUser2', '0x881f5250c08cbf2630b60c9e60a71', '1', null, 'ACCESS', '352', '2020/11/27 12:59', '2020/11/27 12:59'),
	('test59207', 'test', '56a4720be70b6c4f14625effa380349f', 'testUser2', '0xe4a72e5e7a370ab66bfdf11a1759d', '1', null, 'ACCESS', '2887', '2020/11/27 13:04', '2020/11/27 13:04'),
	('test59207', 'test', '56a4720be70b6c4f14625effa380349f', 'testUser2', '0x28fbc5b5672eecda5948b4eb7dd83', '1', null, 'ACCESS', '16', '2020/11/27 13:53', '2020/11/27 13:53'),
	('test59207', 'test', '56a4720be70b6c4f14625effa380349f', 'testUser2', '0x34ef5026ee9d4ec4a058fb2678fc7', '1', null, 'ACCESS', '582', '2020/11/27 13:53', '2020/11/27 13:53'),
	('test59207', 'test', '56a4720be70b6c4f14625effa380349f', 'testUser2', '0x5f477774941c5a6af8263357a433c', '1', null, 'ACCESS', '88', '2020/11/27 14:09', '2020/11/27 14:09'),
	('test59207', 'test', '56a4720be70b6c4f14625effa380349f', 'testUser2', '0xb98c058356c2357e3cb98f94d6009', '1', null, 'ACCESS', '629', '2020/11/27 14:13', '2020/11/27 14:13'),
	('test59207', 'test', '56a4720be70b6c4f14625effa380349f', 'testUser2', '0x8b44a5629c34c8b5b70b845416d64', '1', null, 'ACCESS', '16', '2020/11/27 14:24', '2020/11/27 14:24'),
	('test59207', 'test', '56a4720be70b6c4f14625effa380349f', 'testUser2', '0xd4c29e75042c92d48f3d20ca73302', '1', null, 'ACCESS', '736', '2020/11/27 14:25', '2020/11/27 14:25'),
	('test59207', 'test', '56a4720be70b6c4f14625effa380349f', 'testUser2', '0x92f097ed6e585a353b00fa5a61ecc', '1', null, 'ACCESS', '215', '2020/11/27 17:18', '2020/11/27 17:18'),
	('test59207', 'test', '56a4720be70b6c4f14625effa380349f', 'testUser2', '0x6b83173f2c64b0484d49af49537e2', '1', null, 'ACCESS', '62', '2020/11/27 17:23', '2020/11/27 17:23'),
	('test59207', 'test', '56a4720be70b6c4f14625effa380349f', 'testUser2', '0x7b63b2968f119e5e0acfd086d0ce4', '1', null, 'ACCESS', '355', '2020/11/27 17:24', '2020/11/27 17:24'),
	('test59207', 'test', '56a4720be70b6c4f14625effa380349f', 'testUser2', '0x6bb13a389f38b51af507d4194c5aa', '1', null, 'ACCESS', '326', '2020/11/27 17:31', '2020/11/27 17:31'),
	('test59207', 'test', '56a4720be70b6c4f14625effa380349f', 'testUser2', '0x79c1762b6fbeda308a00b0d807374', '1', null, 'ACCESS', '806', '2020/11/27 17:37', '2020/11/27 17:37'),
	('test59207', 'test', '72838069a5d2bf8a48e0df4b2b46e7b8', 'testUser2', '0xed5330ba71321f4a336352e706291', '1', null, 'ACCESS', '186', '2020/9/23 13:11', '2020/9/23 13:11'),
	('test59207', 'test', '871fcd72ca15e1918815c324a4f02fc1', 'testUser3', '0x2e71d67375485aeb7dd81c518b938', '1', null, 'ACCESS', '34', '2020/9/23 14:06', '2020/9/23 14:06'),
	('test59207', 'test', '871fcd72ca15e1918815c324a4f02fc1', 'testUser3', '0x5bd59fdaf9c55d59129ca4199dacb', '1', null, 'ACCESS', '78', '2020/9/23 14:07', '2020/9/23 14:07'),
	('test59207', 'test', 'bc843f7bfc9597d078088d2e5b916d20', 'testUser4', '0x4b7ff652a365932fbc6c8041e1c6d', '1', null, 'ACCESS', '348', '2020/9/24 11:22', '2020/9/24 11:22'),
	('test59207', 'test', 'bc843f7bfc9597d078088d2e5b916d20', 'testUser4', '0xffc9906349920475bc93b602db660', '1', null, 'ACCESS', '82', '2020/9/24 11:54', '2020/9/24 11:54'),
	('test59207', 'test', 'bc843f7bfc9597d078088d2e5b916d20', 'testUser4', '0xee6f4a3eae513b8ee4b9094314af3', '1', null, 'ACCESS', '150', '2020/11/24 19:28', '2020/11/24 19:28'),
	('test8888', 'test2', '9886782e03e46342081517b498e08243', 'testUser5', '0x894d7ec6a5df1e79ed5596ec0a1f2', '1', null, 'ACCESS', '0', '2020/7/31 22:57', '2020/7/31 22:57'),
	('test8888', 'test2', '9886782e03e46342081517b498e08243', 'testUser5', '0xd5c80a59debc7607763e78a3c3e87', '1', null, 'ACCESS', '0', '2020/7/31 22:57', '2020/7/31 22:57'),
	('test8888', 'test2', '9886782e03e46342081517b498e08243', 'testUser5', '0x6c7ea0dfa836ff895ea9beacaaf37', '1', null, 'ACCESS', '0', '2020/7/31 22:59', '2020/7/31 22:59'),
	('test8888', 'test2', '9886782e03e46342081517b498e08243', 'testUser5', '0x1ce11e610c7c94d27c1dce81ed354', '1', null, 'ACCESS', '0', '2020/8/1 10:29', '2020/8/1 10:29'),
	('test8888', 'test2', '9886782e03e46342081517b498e08243', 'testUser5', '0xc7936dab8e958b4c1f5ff7655ead6', '1', null, 'ACCESS', '0', '2020/7/31 18:51', '2020/7/31 18:51'),
	('test8888', 'test2', '9886782e03e46342081517b498e08243', 'testUser5', '0x80aaf9172efaf97d4e329685cf954', '1', null, 'ACCESS', '0', '2020/7/31 19:16', '2020/7/31 19:16'),
	('test8888', 'test2', '9886782e03e46342081517b498e08243', 'testUser5', '0x7d51b7b6431ef073cdf3d88bc5d13', '1', null, 'ACCESS', '0', '2020/6/22 18:10', '2020/6/22 18:10'),
	('test8888', 'test2', '9886782e03e46342081517b498e08243', 'testUser5', '0xc82c9ce5910381e9b64d7768eeae1', '1', null, 'ACCESS', '0', '2020/6/22 22:19', '2020/6/22 22:19'),
	('test8888', 'test2', '9886782e03e46342081517b498e08243', 'testUser5', '0x9deb68b7bf307c3137c0c052e2a89', '1', null, 'ACCESS', '0', '2020/7/31 19:31', '2020/7/31 19:31'),
	('test8888', 'test2', '9886782e03e46342081517b498e08243', 'testUser5', '0x3dd56f46a1377cbea2fd59fb91637', '1', null, 'ACCESS', '2577', '2020/7/14 18:13', '2020/7/14 18:13'),
	('test8888', 'test2', '9886782e03e46342081517b498e08243', 'testUser5', '0xca75cc93bb780181fc60e463bfbdc', '1', null, 'ACCESS', '870', '2020/6/19 18:28', '2020/6/19 18:28'),
	('test8888', 'test2', '9886782e03e46342081517b498e08243', 'testUser5', '0x39e4e0c3bb4dd397c1c9cb0930f88', '1', null, 'ACCESS', '29', '2020/6/21 16:12', '2020/6/21 16:12'),
	('test8888', 'test2', '9886782e03e46342081517b498e08243', 'testUser5', '0xd5685ee0df2b7cf1c8e54a42e4d60', '1', null, 'ACCESS', '0', '2020/6/22 15:18', '2020/6/22 15:18'),
	('test8888', 'test2', '9886782e03e46342081517b498e08243', 'testUser5', '0xec0b2f3687de00c941d4bc694e430', '1', null, 'ACCESS', '0', '2020/6/22 22:20', '2020/6/22 22:20'),
	('test8888', 'test2', '9886782e03e46342081517b498e08243', 'testUser5', '0xa41c4dfe4dba8f35179be2b45ad50', '1', null, 'ACCESS', '0', '2020/6/22 18:10', '2020/6/22 18:10'),
	('test8888', 'test2', '9886782e03e46342081517b498e08243', 'testUser5', '0xbd2246df453cd32b33048847c4847', '1', null, 'ACCESS', '0', '2020/6/22 18:10', '2020/6/22 18:10'),
	('test8888', 'test2', '9886782e03e46342081517b498e08243', 'testUser5', '0x55662842cd674876fd64cfbfa9551', '1', null, 'ACCESS', '0', '2020/6/22 18:11', '2020/6/22 18:11'),
	('test8888', 'test2', '9886782e03e46342081517b498e08243', 'testUser5', '0x6e26f6e58423e00a1f67a3b49fde5', '1', null, 'ACCESS', '0', '2020/6/22 18:11', '2020/6/22 18:11'),
	('test8888', 'test2', '9886782e03e46342081517b498e08243', 'testUser5', '0xdfffdc2d7015024ba3782459e4820', '1', null, 'ACCESS', '0', '2020/6/22 20:27', '2020/6/22 20:27');
GO
INSERT INTO ODS.EXCEL_DLAUNCH_RPM_MANUAL_INPUT
	(ActivityID, ActivityName, UserId, UserName, H5AccessId, PageCode, Answer, DataType, TimeSpand, LoginDate, CreateDate)
VALUES('test8888', 'test2', '760a1e4f09f5ab657c2b84ab0d104a59', 'testUser6', '0x7693104c459f582dcb32e5bca209d', '1', '3', 'QA2', '0', '2020/3/25 15:38', '2020/3/25 15:38'),
	('test8888', 'test2', 'f2cd689dc75794f97ced37179f305b49', 'testUser7', '0xe11d3dd639b1292fd92dbcc049698', '1', '4', 'QA2', '0', '2020/3/16 12:14', '2020/3/16 12:14');
	

TRUNCATE TABLE ODS.EXCEL_DLAUNCH_LIST;
GO
INSERT INTO ODS.EXCEL_DLAUNCH_LIST
	(ActivityName, ActivityID, ActivityType, ActivityStatus, ActivityTTLUnquiePges, DataSource, StartTime, ProductID, ProductName)
VALUES
	('test2', 'test8888', '1', '3', '15', 'ManualInput', '2020-06-15 15:14:51', null, '肾科'),
	('SDM学习活动', 'AC1626052469167', '2', '3', '26', 'DBM', '2021-07-12 19:44:44', null, '肾科'),
	('test', 'test59207', '1', '3', '8', 'ManualInput', '2020-07-22 22:17:38', null, '肾科'),
	('test', 'test59207', '1', '3', '26', 'DBM', '2020-07-22 22:17:38', null, '肾科'),
	('即用型(RTU)艾司洛尔', 'AC1612262518366', '2', '3', '20', 'DBM', '2021-02-04 18:03:19', null, '围术期'),
	('HDF在线学习活动', 'AC1617022190691', '2', '3', '18', 'DBM', '2021-03-29 20:49:54', null, '肾科'),
	('围术期全程营养管理', 'AC1619615633268', '2', '3', '23', 'DBM', '2021-04-28 21:47:21', null, '围术期'),
	('艾考糊精在线学习活动', 'AC1632297733013', '2', '3', '11', 'DBM', '2021-09-23 23:26:41', NULL, '肾科'),
	('MA_SAVE', 'AC1637312715059', '2', '3', '35', 'DBM', '2021-11-19 17:07:41', NULL, '重症'),
	('血流动力学监测', 'AC1636337726529', '2', '1', null, 'DBM', '2021-11-11 21:51:46.744', null, '围术期')
;

use ChinaDW;
TRUNCATE TABLE ODS.EXCEL_DLAUNCH_PAGE_CODE_FILTER;
GO
INSERT INTO ODS.EXCEL_DLAUNCH_PAGE_CODE_FILTER
	(ActivityID, DataType, DataSource, PageCode, TAG)
VALUES('test8888', 'ACCESS', 'ManualInput', '1', '1'),
	('test8888', 'QA1', 'ManualInput', '1', '1'),
	('test8888', 'QA1', 'ManualInput', '2', '1'),
	('test8888', 'QA1', 'ManualInput', '3', '1'),
	('test8888', 'QA1', 'ManualInput', '4', '1'),
	('test8888', 'QA1', 'ManualInput', '5', '1'),
	('test8888', 'QA2', 'ManualInput', '1', '1'),
	('test8888', 'QA2', 'ManualInput', '2', '1'),
	('test8888', 'QA2', 'ManualInput', '3', '1'),
	('test8888', 'QA2', 'ManualInput', '4', '1'),
	('test8888', 'QA2', 'ManualInput', '5', '1'),
	('test8888', 'QA2', 'ManualInput', '6', '1'),
	('test8888', 'QA2', 'ManualInput', '7', '1'),
	('test8888', 'QA2', 'ManualInput', '8', '1'),
	('test8888', 'QA2', 'ManualInput', '9', '1'),
	('test59207', 'ACCESS', 'ManualInput', '1', '1'),
	('test59207', 'ACCESS', 'DBM', '1', '1'),
	('test59207', 'ACCESS', 'DBM', '2', '1'),
	('test59207', 'ACCESS', 'DBM', '3', '1'),
	('test59207', 'ACCESS', 'DBM', '4', '1'),
	('test59207', 'ACCESS', 'DBM', '5', '1'),
	('test59207', 'ACCESS', 'DBM', '6', '1'),
	('test59207', 'ACCESS', 'DBM', '7', '1'),
	('test59207', 'ACCESS', 'DBM', '8', '1'),
	('test59207', 'ACCESS', 'DBM', '9', '1'),
	('test59207', 'ACCESS', 'DBM', '10', '1'),
	('test59207', 'ACCESS', 'DBM', '11', '1'),
	('test59207', 'ACCESS', 'DBM', '12', '1'),
	('test59207', 'ACCESS', 'DBM', '13', '1'),
	('test59207', 'ACCESS', 'DBM', '14', '1'),
	('test59207', 'ACCESS', 'DBM', '15', '1'),
	('test59207', 'ACCESS', 'DBM', '16', '1'),
	('test59207', 'ACCESS', 'DBM', '17', '1'),
	('test59207', 'ACCESS', 'DBM', '18', '1'),
	('test59207', 'ACCESS', 'DBM', '19', '1'),
	('test59207', 'ACCESS', 'DBM', '20', '1'),
	('test59207', 'ACCESS', 'DBM', '21', '1'),
	('test59207', 'QA1', 'ManualInput', '1', '1'),
	('test59207', 'QA1', 'ManualInput', '2', '1'),
	('test59207', 'QA1', 'ManualInput', '3', '1'),
	('test59207', 'QA2', 'ManualInput', '1', '1'),
	('test59207', 'QA2', 'DBM', '1', '1'),
	('test59207', 'QA2', 'ManualInput', '2', '1'),
	('test59207', 'QA2', 'DBM', '2', '1'),
	('test59207', 'QA2', 'ManualInput', '3', '1'),
	('test59207', 'QA2', 'DBM', '3', '1'),
	('test59207', 'QA2', 'ManualInput', '4', '1'),
	('AC1612262518366', 'ACCESS', 'DBM', '1', '1'),
	('AC1612262518366', 'ACCESS', 'DBM', '2', '1'),
	('AC1612262518366', 'ACCESS', 'DBM', '3', '1'),
	('AC1612262518366', 'ACCESS', 'DBM', '4', '1'),
	('AC1612262518366', 'ACCESS', 'DBM', '5', '1'),
	('AC1612262518366', 'ACCESS', 'DBM', '6', '1'),
	('AC1612262518366', 'ACCESS', 'DBM', '7', '1'),
	('AC1612262518366', 'ACCESS', 'DBM', '8', '1'),
	('AC1612262518366', 'ACCESS', 'DBM', '9', '1'),
	('AC1612262518366', 'ACCESS', 'DBM', '10', '1'),
	('AC1612262518366', 'ACCESS', 'DBM', '11', '1'),
	('AC1612262518366', 'ACCESS', 'DBM', '12', '1'),
	('AC1612262518366', 'ACCESS', 'DBM', '13', '1'),
	('AC1612262518366', 'ACCESS', 'DBM', '14', '1'),
	('AC1612262518366', 'ACCESS', 'DBM', '15', '1'),
	('AC1612262518366', 'QA1', 'DBM', '1', '1'),
	('AC1612262518366', 'QA2', 'DBM', '1', '1'),
	('AC1612262518366', 'QA2', 'DBM', '2', '1'),
	('AC1612262518366', 'QA2', 'DBM', '3', '1'),
	('AC1612262518366', 'QA2', 'DBM', '4', '1'),
	('AC1612262518366', 'QA2', 'DBM', '5', '0'),
	('AC1617022190691', 'ACCESS', 'DBM', '1', '1'),
	('AC1617022190691', 'ACCESS', 'DBM', '2', '1'),
	('AC1617022190691', 'ACCESS', 'DBM', '3', '1'),
	('AC1617022190691', 'ACCESS', 'DBM', '4', '1'),
	('AC1617022190691', 'ACCESS', 'DBM', '5', '1'),
	('AC1617022190691', 'ACCESS', 'DBM', '6', '1'),
	('AC1617022190691', 'ACCESS', 'DBM', '7', '1'),
	('AC1617022190691', 'ACCESS', 'DBM', '8', '1'),
	('AC1617022190691', 'ACCESS', 'DBM', '9', '1'),
	('AC1617022190691', 'ACCESS', 'DBM', '10', '1'),
	('AC1617022190691', 'ACCESS', 'DBM', '11', '1'),
	('AC1617022190691', 'ACCESS', 'DBM', '12', '1'),
	('AC1617022190691', 'ACCESS', 'DBM', '13', '1'),
	('AC1617022190691', 'ACCESS', 'DBM', '14', '1'),
	('AC1617022190691', 'ACCESS', 'DBM', '15', '0'),
	('AC1617022190691', 'QA2', 'DBM', '1', '1'),
	('AC1617022190691', 'QA2', 'DBM', '2', '1'),
	('AC1617022190691', 'QA2', 'DBM', '3', '1'),
	('AC1617022190691', 'QA2', 'DBM', '4', '1'),
	('AC1619615633268', 'ACCESS', 'DBM', '1', '1'),
	('AC1619615633268', 'ACCESS', 'DBM', '2', '1'),
	('AC1619615633268', 'ACCESS', 'DBM', '3', '1'),
	('AC1619615633268', 'ACCESS', 'DBM', '4', '1'),
	('AC1619615633268', 'ACCESS', 'DBM', '5', '1'),
	('AC1619615633268', 'ACCESS', 'DBM', '6', '1'),
	('AC1619615633268', 'ACCESS', 'DBM', '7', '1'),
	('AC1619615633268', 'ACCESS', 'DBM', '8', '1'),
	('AC1619615633268', 'ACCESS', 'DBM', '9', '1'),
	('AC1619615633268', 'ACCESS', 'DBM', '10', '1'),
	('AC1619615633268', 'ACCESS', 'DBM', '11', '1'),
	('AC1619615633268', 'ACCESS', 'DBM', '12', '1'),
	('AC1619615633268', 'ACCESS', 'DBM', '13', '1'),
	('AC1619615633268', 'ACCESS', 'DBM', '14', '1'),
	('AC1619615633268', 'ACCESS', 'DBM', '15', '1'),
	('AC1619615633268', 'ACCESS', 'DBM', '16', '1'),
	('AC1619615633268', 'ACCESS', 'DBM', '17', '1'),
	('AC1619615633268', 'QA1', 'DBM', '1', '1'),
	('AC1619615633268', 'QA2', 'DBM', '1', '1'),
	('AC1619615633268', 'QA2', 'DBM', '2', '1'),
	('AC1619615633268', 'QA2', 'DBM', '3', '1'),
	('AC1619615633268', 'QA2', 'DBM', '4', '1'),
	('AC1619615633268', 'QA2', 'DBM', '5', '1'),
	('AC1626052469167', 'ACCESS', 'DBM', '1', '1'),
	('AC1626052469167', 'ACCESS', 'DBM', '2', '1'),
	('AC1626052469167', 'ACCESS', 'DBM', '3', '1'),
	('AC1626052469167', 'ACCESS', 'DBM', '4', '1'),
	('AC1626052469167', 'ACCESS', 'DBM', '5', '1'),
	('AC1626052469167', 'ACCESS', 'DBM', '6', '1'),
	('AC1626052469167', 'ACCESS', 'DBM', '7', '1'),
	('AC1626052469167', 'ACCESS', 'DBM', '8', '1'),
	('AC1626052469167', 'ACCESS', 'DBM', '9', '1'),
	('AC1626052469167', 'ACCESS', 'DBM', '10', '1'),
	('AC1626052469167', 'ACCESS', 'DBM', '11', '1'),
	('AC1626052469167', 'ACCESS', 'DBM', '12', '1'),
	('AC1626052469167', 'ACCESS', 'DBM', '13', '1'),
	('AC1626052469167', 'ACCESS', 'DBM', '14', '1'),
	('AC1626052469167', 'ACCESS', 'DBM', '15', '1'),
	('AC1626052469167', 'ACCESS', 'DBM', '16', '1'),
	('AC1626052469167', 'ACCESS', 'DBM', '17', '1'),
	('AC1626052469167', 'ACCESS', 'DBM', '18', '1'),
	('AC1626052469167', 'ACCESS', 'DBM', '19', '1'),
	('AC1626052469167', 'ACCESS', 'DBM', '20', '1'),
	('AC1626052469167', 'ACCESS', 'DBM', '21', '1'),
	('AC1626052469167', 'QA1', 'DBM', '1', '1'),
	('AC1626052469167', 'QA1', 'DBM', '2', '1'),
	('AC1626052469167', 'QA2', 'DBM', '1', '1'),
	('AC1626052469167', 'QA2', 'DBM', '2', '1'),
	('AC1626052469167', 'QA2', 'DBM', '3', '1'),
	('AC1632297733013', 'ACCESS', 'DBM', '1', 1),
	('AC1632297733013', 'QA2', 'DBM', '1', 1),
	('AC1632297733013', 'QA1', 'DBM', '1', 1),
	('AC1632297733013', 'QA2', 'DBM', '2', 1),
	('AC1632297733013', 'ACCESS', 'DBM', '2', 1),
	('AC1632297733013', 'ACCESS', 'DBM', '3', 1),
	('AC1632297733013', 'QA2', 'DBM', '3', 1),
	('AC1632297733013', 'ACCESS', 'DBM', '5', 1),
	('AC1632297733013', 'ACCESS', 'DBM', '6', 1),
	('AC1632297733013', 'ACCESS', 'DBM', '8', 1),
	('AC1632297733013', 'ACCESS', 'DBM', '9', 1),
	('AC1637312715059', 'ACCESS', 'DBM', '1', 1),
	('AC1637312715059', 'QA2', 'DBM', '1', 1),
	('AC1637312715059', 'PAGE', 'DBM', '1', 1),
	('AC1637312715059', 'PAGE', 'DBM', '10', 1),
	('AC1637312715059', 'PAGE', 'DBM', '11', 1),
	('AC1637312715059', 'PAGE', 'DBM', '12', 1),
	('AC1637312715059', 'QA2', 'DBM', '2', 1),
	('AC1637312715059', 'PAGE', 'DBM', '2', 1),
	('AC1637312715059', 'ACCESS', 'DBM', '2', 1),
	('AC1637312715059', 'QA2', 'DBM', '3', 1),
	('AC1637312715059', 'ACCESS', 'DBM', '3', 1),
	('AC1637312715059', 'PAGE', 'DBM', '3', 1),
	('AC1637312715059', 'PAGE', 'DBM', '4', 1),
	('AC1637312715059', 'QA2', 'DBM', '4', 1),
	('AC1637312715059', 'ACCESS', 'DBM', '4', 1),
	('AC1637312715059', 'QA2', 'DBM', '5', 1),
	('AC1637312715059', 'ACCESS', 'DBM', '5', 1),
	('AC1637312715059', 'PAGE', 'DBM', '5', 1),
	('AC1637312715059', 'QA2', 'DBM', '6', 1),
	('AC1637312715059', 'ACCESS', 'DBM', '6', 1),
	('AC1637312715059', 'PAGE', 'DBM', '6', 1),
	('AC1637312715059', 'PAGE', 'DBM', '7', 1),
	('AC1637312715059', 'ACCESS', 'DBM', '7', 1),
	('AC1637312715059', 'PAGE', 'DBM', '8', 1),
	('AC1637312715059', 'PAGE', 'DBM', '9', 1),
	('AC1637312715059', 'VIDEO', 'DBM', 'v1307200832001', 1),
	('AC1637312715059', 'PAGE', 'DBM', 'v1307200832001', 1),
	('AC1637312715059', 'VIDEO', 'DBM', 'v1307200832002', 1),
	('AC1637312715059', 'PAGE', 'DBM', 'v1307200832002', 1),
	('AC1637312715059', 'PAGE', 'DBM', 'v1307200832003', 1),
	('AC1637312715059', 'VIDEO', 'DBM', 'v1307200832003', 1),
	('AC1637312715059', 'VIDEO', 'DBM', 'v1307200832004', 1),
	('AC1637312715059', 'PAGE', 'DBM', 'v1307200832004', 1),
	('AC1637312715059', 'PAGE', 'DBM', 'v1307200832005', 1),
	('AC1637312715059', 'VIDEO', 'DBM', 'v1307200832005', 1)
;
use ChinaDW;
GO
UPDATE ODS.DW_SP_INFO SET IsDeleted =1 WHERE Project = 'Dlaunch';
GO
INSERT INTO ODS.DW_SP_INFO
	(ProcedureName, Operation, TargetTable, SourceTableView, Project)
VALUES('DW.SP_DIM_DLAUNCH_LIST', 'TRUNCATE', 'DW.DIM_DLAUNCH_LIST', 'ODS.EXCEL_DLAUNCH_LIST', 'DLaunch'),
	('DW.SP_DIM_DLAUNCH_USER_INFO', 'TRUNCATE', 'DW.DIM_DLAUNCH_USER_INFO', 'DW.VW_T_DIM_DLAUNCH_USER_INFO', 'DLaunch'),
	('DW.SP_FACT_DLAUNCH_VISIT_RECORD', 'TRUNCATE', 'DW.FACT_DLAUNCH_VISIT_RECORD', 'STG.DBM_DLAUNCH_VISIT_RECORD', 'DLaunch'),
	('DW.SP_FACT_DLAUNCH_VISIT_SUMMARY', 'TRUNCATE', 'DW.FACT_DLAUNCH_VISIT_SUMMARY', 'DW.VW_T_FACT_DBM_DLAUNCH_VISIT_SUMMARY', 'DLaunch'),
	('ODS.SP_DBM_ACT_DLAUNCH_ANSWER_RECORD_INITIALIZE', 'TRUNCATE', 'ODS.DBM_ACT_DLAUNCH_ANSWER_RECORD', 'OPENQUERY.CHINADBM.MS009_ACTMGMT.ACT_DLAUNCH_ANSWER_RECORD', 'DLaunch'),
	('ODS.SP_DBM_ACT_DLAUNCH_ACCESS_RECORD_INITIALIZE', 'TRUNCATE', 'ODS.DBM_ACT_DLAUNCH_ACCESS_RECORD', 'OPENQUERY.CHINADBM.MS009_ACTMGMT.ACT_DLAUNCH_ACCESS_RECORD', 'DLaunch'),
	('ODS.SP_DBM_ACT_DLAUNCH_VIDEO_RECORD_INITIALIZE', 'TRUNCATE', 'ODS.DBM_ACT_DLAUNCH_VIDEO_RECORD', 'OPENQUERY.CHINADBM.MS009_ACTMGMT.ACT_DLAUNCH_VIDEO_RECORD', 'DLaunch'),
	('ODS.SP_DBM_ACT_DLAUNCH_PAGE_RECORD_INITIALIZE', 'TRUNCATE', 'ODS.DBM_ACT_DLAUNCH_PAGE_RECORD', 'OPENQUERY.CHINADBM.MS009_ACTMGMT.ACT_DLAUNCH_PAGE_RECORD', 'DLaunch'),
	('ODS.SP_DBM_ACT_DLAUNCH_ACCESS_RECORD', 'UPDATE', 'ODS.DBM_ACT_DLAUNCH_ACCESS_RECORD', 'OPENQUERY.CHINADBM.MS009_ACTMGMT.ACT_DLAUNCH_ACCESS_RECORD', 'DLaunch'),
	('ODS.SP_DBM_ACT_DLAUNCH_ANSWER_RECORD', 'UPDATE', 'ODS.DBM_ACT_DLAUNCH_ANSWER_RECORD', 'OPENQUERY.CHINADBM.MS009_ACTMGMT.ACT_DLAUNCH_ANSWER_RECORD', 'DLaunch'),
	('ODS.SP_DBM_ACT_DLAUNCH_PAGE_RECORD', 'UPDATE', 'ODS.DBM_ACT_DLAUNCH_PAGE_RECORD', 'OPENQUERY.CHINADBM.MS009_ACTMGMT.ACT_DLAUNCH_PAGE_RECORD', 'DLaunch'),
	('ODS.SP_DBM_ACT_DLAUNCH_VIDEO_RECORD', 'UPDATE', 'ODS.DBM_ACT_DLAUNCH_VIDEO_RECORD', 'OPENQUERY.CHINADBM.MS009_ACTMGMT.ACT_DLAUNCH_VIDEO_RECORD', 'DLaunch'),
	('STG.SP_DBM_DLAUNCH_VISIT_RECORD', 'TRUNCATE', 'STG.DBM_DLAUNCH_VISIT_RECORD', 'STG.VW_T_DBM_DLAUNCH_RECORD', 'DLaunch'),
	--! DW_C_DLaunch_2021221更新
	('ODS.SP_DBM_ACT_DLAUNCH_GAME_RECORD_INITIALIZE', 'TRUNCATE+INSERT', 'ODS.DBM_ACT_DLAUNCH_GAME_RECORD', 'ChinaDBM.ms009_actmgmt.act_dlaunch_game_record', 'DLaunch'),
	('ODS.SP_DBM_ACT_DLAUNCH_GAME_RECORD', 'TRUNCATE+INSERT', 'ODS.DBM_ACT_DLAUNCH_GAME_RECORD', 'ChinaDBM.ms009_actmgmt.act_dlaunch_game_record', 'DLaunch')
--! DW_C_DLaunch_2021221更新
;

GO
-- =============================================
-- Author:		<Jec Wang>
-- Create date: <2021-10-14>
-- Description:	<TABLEAU DLAUNCH DASHBOARD>
-- 通过chinaDBM数据库中提取 
-- ms009_actmgmt.act_dlaunch_video_record 
-- 初始化ODS.DBM_ACT_DLAUNCH_ANSWER_RECORD
-- =============================================
CREATE PROCEDURE ODS.SP_DBM_ACT_DLAUNCH_ANSWER_RECORD_INITIALIZE
	(@sourceCount INT OUTPUT,
	@deleteCount INT OUTPUT,
	@insertCount INT OUTPUT,
	@updateCount INT OUTPUT)
AS
BEGIN
	SELECT @sourceCount = COUNT(1)
	FROM ODS.DBM_ACT_DLAUNCH_ANSWER_RECORD;
	SET XACT_ABORT on;
	BEGIN TRAN myTran;
	TRUNCATE TABLE ODS.DBM_ACT_DLAUNCH_ANSWER_RECORD;
	INSERT INTO ODS.DBM_ACT_DLAUNCH_ANSWER_RECORD
		(
		id,h5_access_id,activity_id,activity_name,user_id,user_name,
		question_category,question_type,question_code,answer_record,
		product_id,product_name,create_time
		)
	SELECT
		id, h5_access_id, activity_id, activity_name, user_id, user_name,
		question_category, question_type, question_code, answer_record,
		product_id, product_name, create_time
	FROM OPENQUERY (CHINADBM,'SELECT * FROM ms009_actmgmt.act_dlaunch_answer_record;');
	COMMIT TRAN myTran;
	SET @insertCount = @@ROWCOUNT;
END;


GO
-- =============================================
-- Author:		<Jec Wang>
-- Create date: <2021-10-14>
-- Description:	<TABLEAU DLAUNCH DASHBOARD>
-- 通过chinaDBM数据库中提取 
-- ms009_actmgmt.act_dlaunch_video_record 
-- 初始化ODS.DBM_ACT_DLAUNCH_ACCESS_RECORD
-- =============================================
CREATE PROCEDURE ODS.SP_DBM_ACT_DLAUNCH_ACCESS_RECORD_INITIALIZE
	(@sourceCount INT OUTPUT,
	@deleteCount INT OUTPUT,
	@insertCount INT OUTPUT,
	@updateCount INT OUTPUT)
AS
BEGIN
	SELECT @sourceCount = COUNT(1)
	FROM ODS.DBM_ACT_DLAUNCH_ACCESS_RECORD;
	SET XACT_ABORT on;
	BEGIN TRAN myTran;
	TRUNCATE TABLE ODS.DBM_ACT_DLAUNCH_ACCESS_RECORD;
	INSERT INTO ODS.DBM_ACT_DLAUNCH_ACCESS_RECORD
		(
		id,page_access_id,activity_id,activity_name,user_id,user_name,
		login_data,login_time,page_code,h5_access_id,
		product_id,product_name,create_time,logout_data,logout_time
		)
	SELECT
		id, page_access_id, activity_id, activity_name, user_id, user_name, login_data, login_time, page_code,
		h5_access_id, product_id, product_name, create_time, logout_data, logout_time
	FROM OPENQUERY (CHINADBM,'SELECT * FROM ms009_actmgmt.act_dlaunch_access_record;');
	COMMIT TRAN myTran;
	SET @insertCount = @@ROWCOUNT;
END;



GO
-- =============================================
-- Author:		<Jec Wang>
-- Create date: <2021-10-14>
-- Description:	<TABLEAU DLAUNCH DASHBOARD>
-- 通过chinaDBM数据库中提取 
-- ms009_actmgmt.act_dlaunch_video_record 
-- 初始化ODS.DBM_ACT_DLAUNCH_VIDEO_RECORD
-- =============================================
CREATE PROCEDURE ODS.SP_DBM_ACT_DLAUNCH_VIDEO_RECORD_INITIALIZE
	(@sourceCount INT OUTPUT,
	@deleteCount INT OUTPUT,
	@insertCount INT OUTPUT,
	@updateCount INT OUTPUT)
AS
BEGIN
	SELECT @sourceCount = COUNT(1)
	FROM ODS.DBM_ACT_DLAUNCH_VIDEO_RECORD;
	SET XACT_ABORT on;
	BEGIN TRAN myTran;
	TRUNCATE TABLE ODS.DBM_ACT_DLAUNCH_VIDEO_RECORD
	INSERT INTO ODS.DBM_ACT_DLAUNCH_VIDEO_RECORD
		(
		id,page_access_id,activity_id,activity_name,user_id,user_name,
		video_code,view_length,h5_access_id,product_id,product_name,create_time
		)
	SELECT
		id, page_access_id, activity_id, activity_name, user_id, user_name,
		video_code, view_length, h5_access_id, product_id, product_name, create_time
	FROM OPENQUERY (CHINADBM,'SELECT * FROM ms009_actmgmt.act_dlaunch_video_record;')
	COMMIT TRAN myTran;
	SET @insertCount = @@ROWCOUNT;
END;


GO
-- =============================================
-- Author:		<Jec Wang>
-- Create date: <2021-10-14>
-- Description:	<TABLEAU DLAUNCH DASHBOARD>
-- 通过chinaDBM数据库中提取 
-- ms009_actmgmt.act_dlaunch_video_record 
-- 初始化ODS.DBM_ACT_DLAUNCH_PAGE_RECORD
-- =============================================
CREATE PROCEDURE ODS.SP_DBM_ACT_DLAUNCH_PAGE_RECORD_INITIALIZE
	(@sourceCount INT OUTPUT,
	@deleteCount INT OUTPUT,
	@insertCount INT OUTPUT,
	@updateCount INT OUTPUT)
AS
BEGIN
	SELECT @sourceCount = COUNT(1)
	FROM ODS.DBM_ACT_DLAUNCH_VIDEO_RECORD;
	SET XACT_ABORT on;
	BEGIN TRAN myTran;
	TRUNCATE TABLE ODS.DBM_ACT_DLAUNCH_PAGE_RECORD
	INSERT INTO ODS.DBM_ACT_DLAUNCH_PAGE_RECORD
		(
		id, activity_id, activity_name, user_id, user_name,
		page_code, status, h5_access_id, product_id, product_name, create_time
		)
	SELECT
		id, activity_id, activity_name, user_id, user_name,
		page_code, status, h5_access_id, product_id, product_name, create_time
	FROM OPENQUERY (CHINADBM,'SELECT * FROM ms009_actmgmt.act_dlaunch_page_record;')
	COMMIT TRAN myTran;
	SET @insertCount = @@ROWCOUNT;
END;
GO
-- =============================================
-- Author:		<Jec Wang>
-- Create date: <2021-10-14>
-- Description:	<TABLEAU DLAUNCH DASHBOARD>
-- 通过chinaDBM数据库中提取 
-- ms009_actmgmt.act_dlaunch_access_record 
-- 的近两日数据，并更新至 ODS.DBM_ACT_DLAUNCH_ACCESS_RECORD
-- =============================================
CREATE PROCEDURE ODS.SP_DBM_ACT_DLAUNCH_ACCESS_RECORD
	(@sourceCount INT OUTPUT,
	@deleteCount INT OUTPUT,
	@insertCount INT OUTPUT,
	@updateCount INT OUTPUT)
AS
BEGIN
	SELECT @sourceCount = COUNT(1)
	FROM ODS.DBM_ACT_DLAUNCH_ACCESS_RECORD;
	IF @sourceCount > 0
	BEGIN
		SET XACT_ABORT ON;
		BEGIN TRAN myTran;
		MERGE INTO ODS.DBM_ACT_DLAUNCH_ACCESS_RECORD target
			USING (
			SELECT *
		FROM OPENQUERY (CHINADBM,'SELECT * FROM ms009_actmgmt.act_dlaunch_access_record
			where create_time > date_add(now(),interval -2 DAY)
			;')) sources
			ON (target.id collate SQL_Latin1_General_CP1_CI_AS = sources.id collate SQL_Latin1_General_CP1_CI_AS)
			WHEN MATCHED THEN 
				UPDATE 
				SET target.page_access_id = sources.page_access_id,
					target.activity_id = sources.activity_id,
					target.activity_name = sources.activity_name,
					target.user_id = sources.user_id,
					target.login_data = sources.login_data,
					target.login_time = sources.login_time,
					target.page_code = sources.page_code,
					target.h5_access_id = sources.h5_access_id,
					target.product_id = sources.product_id,
					target.product_name = sources.product_name,
					target.create_time = sources.create_time,
					target.logout_data = sources.logout_data,
					target.logout_time = sources.logout_time
			WHEN NOT MATCHED THEN 
				INSERT(	id,
						page_access_id,activity_id,
						activity_name,user_id,user_name,
						login_data,login_time,page_code,
						h5_access_id,product_id,
						product_name,create_time,
						logout_data,logout_time  )
				VALUES( id,
						page_access_id,activity_id,
						activity_name,user_id,user_name,
						login_data,login_time,page_code,
						h5_access_id,product_id,
						product_name,create_time,
						logout_data,logout_time);
		COMMIT TRAN myTran;
		SET @insertCount = @@ROWCOUNT;
	END;
END;
GO
-- =============================================
-- Author:		<Jec Wang>
-- Create date: <2021-10-14>
-- Description:	<TABLEAU DLAUNCH DASHBOARD>
-- 通过chinaDBM数据库中提取 
-- ms009_actmgmt.act_dlaunch_answer_record
-- 的近两日数据，并更新至 ODS.DBM_ACT_DLAUNCH_ANSWER_RECORD
-- =============================================
CREATE PROCEDURE ODS.SP_DBM_ACT_DLAUNCH_ANSWER_RECORD
	(@sourceCount INT OUTPUT,
	@deleteCount INT OUTPUT,
	@insertCount INT OUTPUT,
	@updateCount INT OUTPUT)
AS
BEGIN
	SELECT @sourceCount = COUNT(1)
	FROM ODS.DBM_ACT_DLAUNCH_ANSWER_RECORD;
	IF @sourceCount > 0
	BEGIN
		SET XACT_ABORT ON;
		BEGIN TRAN myTran;
		MERGE INTO ODS.DBM_ACT_DLAUNCH_ANSWER_RECORD target
			USING (
			SELECT id, h5_access_id, activity_id, activity_name, user_id, user_name,
			question_category, question_type, question_code,
			answer_record, product_id, product_name, create_time
		FROM OPENQUERY (CHINADBM,'SELECT * FROM ms009_actmgmt.act_dlaunch_answer_record 
							where create_time > date_add(now(),interval -2 DAY);')) sources
			ON (target.id collate SQL_Latin1_General_CP1_CI_AS = sources.id collate SQL_Latin1_General_CP1_CI_AS )
			WHEN MATCHED THEN 
				UPDATE 
				SET target.id = sources.id,
					target.h5_access_id = sources.h5_access_id,
					target.activity_id = sources.activity_id,
					target.activity_name = sources.activity_name,
					target.user_id = sources.user_id,
					target.user_name = sources.user_name,
					target.question_category = sources.question_category,
					target.question_type = sources.question_type,
					target.question_code = sources.question_code,
					target.answer_record = sources.answer_record,
					target.product_id = sources.product_id,
					target.product_name = sources.product_name,
					target.create_time = sources.create_time
			WHEN NOT MATCHED THEN 
				INSERT(	id,h5_access_id,activity_id,activity_name,user_id,user_name,question_category,
						question_type,question_code,answer_record,product_id,product_name,create_time)
					VALUES( id,h5_access_id,activity_id,activity_name,user_id,user_name,question_category,
						question_type,question_code,answer_record,product_id,product_name,create_time);
		COMMIT TRAN myTran;
		SET @insertCount = @@ROWCOUNT;
	END;
END;
GO
-- =============================================
-- Author:		<Jec Wang>
-- Create date: <2021-10-14>
-- Description:	<TABLEAU DLAUNCH DASHBOARD>
-- 通过chinaDBM数据库中提取 
-- ms009_actmgmt.act_dlaunch_page_record 
-- 的近两日数据，并更新至 ODS.DBM_ACT_DLAUNCH_PAGE_RECORD
-- =============================================
CREATE PROCEDURE ODS.SP_DBM_ACT_DLAUNCH_PAGE_RECORD
	(@sourceCount INT OUTPUT,
	@deleteCount INT OUTPUT,
	@insertCount INT OUTPUT,
	@updateCount INT OUTPUT)
AS
BEGIN
	SELECT @sourceCount = COUNT(1)
	FROM ODS.DBM_ACT_DLAUNCH_PAGE_RECORD;
	IF @sourceCount > 0
	BEGIN
		SET XACT_ABORT ON;
		BEGIN TRAN myTran;
		MERGE INTO ODS.DBM_ACT_DLAUNCH_PAGE_RECORD target
			USING (
			SELECT id, activity_id, activity_name, user_id, user_name, page_code, status,
			h5_access_id, product_id, product_name, create_time
		FROM OPENQUERY (CHINADBM,'SELECT * FROM ms009_actmgmt.act_dlaunch_page_record 
							where create_time > date_add(now(),interval -2 DAY);')) sources
			ON (target.id collate SQL_Latin1_General_CP1_CI_AS = sources.id collate SQL_Latin1_General_CP1_CI_AS )
			WHEN MATCHED THEN 
				UPDATE 
				SET target.id = sources.id,
					target.activity_id = sources.activity_id,
					target.activity_name = sources.activity_name,
					target.user_id = sources.user_id,
					target.user_name = sources.user_name,
					target.page_code = sources.page_code,
					target.status = sources.status,
					target.h5_access_id = sources.h5_access_id,
					target.product_id = sources.product_id,
					target.product_name = sources.product_name,
					target.create_time = sources.create_time
			WHEN NOT MATCHED THEN 
				INSERT(	id,activity_id,activity_name,user_id,user_name,page_code,status,
						h5_access_id,product_id,product_name,create_time)
					VALUES( id,activity_id,activity_name,user_id,user_name,page_code,status,
						h5_access_id,product_id,product_name,create_time);
		COMMIT TRAN myTran;
		SET @insertCount = @@ROWCOUNT;
	END;
END;
GO
-- =============================================
-- Author:		<Jec Wang>
-- Create date: <2021-10-14>
-- Description:	<TABLEAU DLAUNCH DASHBOARD>
-- 通过chinaDBM数据库中提取 
-- ms009_actmgmt.act_dlaunch_video_record 
-- 的近两日数据，并更新至 ODS.DBM_ACT_DLAUNCH_VIDEO_RECORD
-- =============================================
CREATE PROCEDURE ODS.SP_DBM_ACT_DLAUNCH_VIDEO_RECORD
	(@sourceCount INT OUTPUT,
	@deleteCount INT OUTPUT,
	@insertCount INT OUTPUT,
	@updateCount INT OUTPUT)
AS
BEGIN
	SELECT @sourceCount = COUNT(1)
	FROM ODS.DBM_ACT_DLAUNCH_VIDEO_RECORD;
	IF @sourceCount > 0
	BEGIN
		SET XACT_ABORT ON;
		BEGIN TRAN myTran;
		MERGE INTO ODS.DBM_ACT_DLAUNCH_VIDEO_RECORD target
			USING (
			SELECT id, page_access_id, activity_id, activity_name, user_id, user_name,
			video_code, view_length, h5_access_id, product_id, product_name, create_time
		FROM OPENQUERY (CHINADBM,'SELECT * FROM ms009_actmgmt.act_dlaunch_video_record
							where create_time > date_add(now(),interval -2 DAY);')) sources
			ON (target.id collate SQL_Latin1_General_CP1_CI_AS = sources.id collate SQL_Latin1_General_CP1_CI_AS )
			WHEN MATCHED THEN 
				UPDATE 
				SET target.id = sources.id,
					target.page_access_id = sources.page_access_id,
					target.activity_id = sources.activity_id,
					target.activity_name = sources.activity_name,
					target.user_id = sources.user_id,
					target.user_name = sources.user_name,
					target.video_code = sources.video_code,
					target.view_length = sources.view_length,
					target.h5_access_id = sources.h5_access_id,
					target.product_id = sources.product_id,
					target.product_name = sources.product_name,
					target.create_time = sources.create_time
			WHEN NOT MATCHED THEN 
				INSERT(	id, page_access_id, activity_id, activity_name, user_id, user_name, 
						video_code, view_length, h5_access_id, product_id, product_name, create_time)
					VALUES( id, page_access_id, activity_id, activity_name, user_id, user_name, 
						video_code, view_length, h5_access_id, product_id, product_name, create_time);
		COMMIT TRAN myTran;
		SET @insertCount = @@ROWCOUNT;
	END;
END;

GO
CREATE TABLE STG.DBM_DLAUNCH_VISIT_RECORD
(
	ActivityID VARCHAR(64),
	ActivityName VARCHAR(256),
	UserId VARCHAR(64),
	UserName VARCHAR(128),
	H5AccessId VARCHAR(64),
	PageCode VARCHAR(50),
	DataType VARCHAR(20),
	TimeSpand INT,
	LoginDate DATETIME,
	CreateDate DATETIME,
	DataSource varchar(30)
);
--! DW_C_DLaunch_2021221  to convent varchar '27.666' in to number
ALTER TABLE STG.DBM_DLAUNCH_VISIT_RECORD ALTER COLUMN TimeSpand FLOAT;
GO
-- 将 Access_record / answer_record / page_record / vedio_reocrd 合并为一张表
-- 便于后续统计页面数(问题回答数量，视频观看数量，页码阅读数量等 （现阶段仅对独立页面数量进行计数，）)
-- 2021221 修改Video表中时间字段，强转为Float型数据 video211221
CREATE VIEW STG.VW_T_DBM_DLAUNCH_RECORD
as
			SELECT Activity_Id ActivityID, Activity_Name ActivityName, User_Id UserId, User_Name UserName,
			H5_Access_Id H5AccessId, page_Code PageCode, DataType,
			Time_Spand TimeSpand, Login_Date LoginDate,
			cast(Create_Date as datetime) CreateDate ,
			'DBM' DataSource
		-- 区分数据来源
		FROM (
				SELECT activity_id, activity_name, user_id, user_name, h5_access_id, page_code, DataType, time_spand,
					login_date, create_date
				FROM (
													SELECT activity_id, activity_name , user_id , user_name , h5_access_id ,
							page_code,
							'PAGE' DataType, 5 time_spand, -- TODO 需要统一统计口径
							null login_date, create_time create_date
						FROM ods.DBM_ACT_DLAUNCH_PAGE_RECORD ddpr -- TODO 替换数据库和表名
							INNER join ods.EXCEL_DLAUNCH_LIST edl1 on edl1.ActivityID = ddpr.activity_id
					UNION all
						SELECT
							activity_id, activity_name , user_id , user_name , h5_access_id ,
							page_code, DataType, sum(time_spand) time_spand, -- 需要统一统计口径
							null login_date , MIN(create_time) create_date
						FROM (
			SELECT activity_id, activity_name, user_id, user_name, h5_access_id,
								question_code page_code,
								case question_category when 1 then 'QA1' when 2 then 'QA2'
					else 'QA0' end as DataType, 0 time_spand, -- TODO 需要统一统计口径
								create_time
							FROM ods.DBM_ACT_DLAUNCH_ANSWER_RECORD dadar -- TODO 替换数据库和表名
								INNER join ods.EXCEL_DLAUNCH_LIST edl2 on edl2.ActivityID = dadar.activity_id
		) QA1
						group by activity_id, activity_name , user_id , user_name,
				h5_access_id , page_code, DataType
					UNION all
						SELECT activity_id, activity_name, user_id , user_name, h5_access_id, page_code, DataType, sum(time_spand) time_spand,
							min(login_datatime) login_datatime, min(create_datetime) create_datetime
						FROM (
			SELECT activity_id, activity_name, user_id , user_name, h5_access_id,
								page_code , 'ACCESS' DataType, 5 time_spand,
								-- 数据源中存在24时的数据，将其替换为00
								cast(cast(login_data as date) as datetime) + 
					cast(cast(
					case when len(login_time) < 7 then login_time -- sql server中，时间类型会自动补0例如 2:8:7则会被转为 02:08:07
					else 
					case when left(login_time,2) = '24' then '00' + right(replace(login_time,' ',''),6) -- 时间标注为24时报错，替换为00 TODO 需要核实
					else login_time end
					end as time) as datetime) login_datatime ,
								create_time create_datetime
							FROM ods.DBM_ACT_DLAUNCH_ACCESS_RECORD adar
								INNER join ods.EXCEL_DLAUNCH_LIST edl3 on edl3.ActivityID = adar.activity_id
							where year( login_data) > 2000 -- 数据源中存在 0011和 0010年的数据
		) acc1
						group by activity_id, activity_name, user_id , user_name, h5_access_id, page_code, DataType
) record1
			UNION all
				SELECT
					activity_id, activity_name, user_id, user_name, h5_access_id,
					video_code page_code, 'VIDEO' DataType,
					--!--------由于DBM数据类型发生变化，导致DW中数据类型转换出现错误------DW_C_DLaunch_2021221-----------
					cast(view_length as FLOAT) timespand ,
					--!--------由于DBM数据类型发生变化，导致DW中数据类型转换出现错误------DW_C_DLaunch_2021221-----------
					null login_date, create_time
				FROM ODS.DBM_ACT_DLAUNCH_VIDEO_RECORD dadvr -- TODO 替换数据库和表名
					INNER join ods.EXCEL_DLAUNCH_LIST edl4 on edl4.ActivityID = dadvr.activity_id
) union_data
	UNION ALL
		SELECT ActivityId, ActivityName, userId, UserName, H5AccessId, PageCode, DataType,
			TimeSpand, LoginDate, CreateDate, 'ManualInput' DATASOURCE
		FROM ODS.EXCEL_DLAUNCH_RPM_MANUAL_INPUT -- TODO 看是否需要加
;
GO
-- =============================================
-- Author:		<Jec Wang>
-- Create date: <2021-10-12>
-- Description:	<TABLEAU DLAUNCH DASHBOARD>
-- 				用户访问日志汇总
-- =============================================
CREATE PROCEDURE STG.SP_DBM_DLAUNCH_VISIT_RECORD
	(@sourceCount INT OUTPUT,
	@deleteCount INT OUTPUT,
	@insertCount INT OUTPUT,
	@updateCount INT OUTPUT)
AS
BEGIN
	SELECT @sourceCount = COUNT(1)
	FROM STG.DBM_DLAUNCH_VISIT_RECORD;
	SET XACT_ABORT on;
	BEGIN TRAN myTran;
	TRUNCATE TABLE  STG.DBM_DLAUNCH_VISIT_RECORD
	INSERT INTO  
			STG.DBM_DLAUNCH_VISIT_RECORD
		(
		ActivityID,ActivityName,UserId,UserName,H5AccessId,PageCode,DataType,TimeSpand,LoginDate,CreateDate,DataSource )
	SELECT ActivityID, ActivityName, UserId, UserName, H5AccessId, PageCode, DataType, TimeSpand, LoginDate, CreateDate, DataSource
	FROM STG.VW_T_DBM_DLAUNCH_RECORD
	COMMIT TRAN myTran;
	SET @insertCount = @@ROWCOUNT;
END;

GO
-- 创建用户属性表
CREATE TABLE DW.DIM_DLAUNCH_USER_INFO
(
	UserId VARCHAR(128),
	UserName NVARCHAR(64),
	Type NVARCHAR(64),
	Gender NVARCHAR(64),
	Department NVARCHAR(64),
	Profession NVARCHAR(64),
	HospitalName VARCHAR(128),
	Province VARCHAR(128),
	City VARCHAR(128),
	delete_status INT,
	IsBboost VARCHAR(2),
	HospitalLevel VARCHAR (128)
);
GO
-- 创建处理后的活动日志表
CREATE TABLE DW.FACT_DLAUNCH_VISIT_RECORD
(
	ActivityID VARCHAR(64),
	ActivityName VARCHAR(256),
	UserId VARCHAR(64),
	UserName VARCHAR(128),
	H5AccessId VARCHAR(64),
	PageCode VARCHAR(50),
	DataType VARCHAR(20),
	TimeSpand INT,
	LoginDate DATETIME,
	CreateDate DATETIME,
	DataSource varchar(30)
);
GO
--! DW_C_DLaunch_2021221 
ALTER TABLE ChinaDW.DW.FACT_DLAUNCH_VISIT_RECORD ALTER COLUMN TimeSpand FLOAT;
GO
-- 创建最大页面数访问记录
CREATE TABLE DW.FACT_DLAUNCH_VISIT_SUMMARY
(
	ActivityID VARCHAR(128),
	UserID VARCHAR(128),
	H5AccessID VARCHAR(128),
	DataType VARCHAR(20),
	PageCode VARCHAR(50),
	CreateDate DATETIME,
	MaxAccessedPageCode int,
	PageHistoryAccessTimes int,
	DataSource varchar(20)
);
GO
CREATE TABLE DW.DIM_DLAUNCH_LIST
(
	ActivityName varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	ActivityID varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	ActivityType int NULL,
	ActivityStatus int NULL,
	ActivityTTLUnquiePges int NULL,
	DataSource VARCHAR(50) ,
	StartTime datetime NULL,
	ProductID varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
	ProductName varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
);
GO
-- 合并User信息和医院信息
CREATE VIEW DW.VW_T_DIM_DLAUNCH_USER_INFO
as
	select UserId, UserName, Type,
		CASE WHEN Gender is not null THEN Gender Else '未知' end Gender,
		Department,
		-- 当TYPE中职务与Profession不一致时，取Type中第一个字 + '师'
		-- 当条件为主管护师+医生时， 用医师表示
		CASE WHEN profession='主管护师' AND [type] ='医生' THEN '医师' 
		--当条件为护师+医生时， 用医师表示
     	WHEN profession='护师' AND [type] ='医生' THEN '医师'
     	--当条件为'医师','主任医师','主治医师','住院医','副主任医师'并且是'护士'时，用'护师'表示，其余情况还是用profession表示
     	WHEN profession IN ('医师','主任医师','主治医师','住院医','副主任医师') AND [type] ='护士' THEN '护师' ELSE profession 
     	END AS Profession,
		HospitalName, Province, City, du.delete_status,
		case when boost_id is not null then '是' else '否' end as IsBboost,
		concat(
		case when grade is null then '其他' else grade end, 
		case when level is null then '其他' else level end 
		) HospitalLevel
	from (
	-- 用户信息表中有效的记录（避免被删除的记录造成数据重复），
	-- 有一部分记录可能由于数据源问题，造成完全相同的记录有两条（包括create time） 因此需要额外去重
	select
			DISTINCT dmu1.id UserId,
			dmu2.name UserName, dmu2.sex gender, dmu2.hospital_id,
			dmu2.department, dmu2.profession, dmu2.update_time, dmu2.boost_id, dmu2.boost_hospital_name,
			dmu2.boost_name, dmu2.delete_status, dmu2.Type, dmu2.hospital_sync_code
		from (
		-- 如果id存在多条记录，那么取注册时间离现在最近的一条(时间最大)
		SELECT id, max(Register_time) Register_time
			from STG.DBM_MIN_USER DMU
				RIGHT join STG.DBM_DLAUNCH_VISIT_RECORD ddvr on ddvr.UserId = DMU.id
			WHERE DELETE_STATUS = 1
			group by Id
			) dmu1
			left join STG.DBM_MIN_USER dmu2 -- TODO 替换表名称
			on dmu1.id = dmu2.id and dmu1.Register_time = dmu2.Register_time
) du
		left join (
	-- 与user表相同，如果医院存在重复值则需要去重
	-- 通过sync_code和hospital_id进行匹配
	select DISTINCT id , sync_code , hospital_code , name HospitalName ,
			province , city , district , delete_status, grade, level
		from ODS.DBM_SYS_HOSPITAL
		-- TODO 替换表名称
		WHERE delete_status = 1
) hos on du.hospital_sync_code = hos.sync_code and du.hospital_id = hos.id
;

GO
-- DM.DBM_ACT_ROOLING_ACCESS_INFO
-- 获取用户在不同时间节点中最大的访问页面数和页面重复访问次数
-- 1.自链接通过调节找到时间A大于等于B，（时间A之前全部的用户访问记录）
-- 2.通过分组查询，获取到每个用户对于每个活动的在某一时间点之前的信息
-- 通过分组表对各项分组项进行条件排除（例如活动，用户，数据源等等）
-- 确定条件后，再对符合条件的数据进行统计（例如页面计数等）
CREATE VIEW DW.VW_T_FACT_DBM_DLAUNCH_VISIT_SUMMARY
as
	select uuf1.ActivityID, uuf1.UserID, uuf1.H5AccessID, uuf1.DataType, uuf1.DataSource,
		uuf1.PageCode, uuf1.CreateDate,
		count(distinct CASE WHEN uuf2.DataType IS NOT NULL 
							THEN concat(uuf2.DataType, uuf2.PageCode)
							END
							) MaxAccessedPageCode, -- 窗口内最大访问页数
		count(	case 
				-- 
				when uuf1.PageCode=uuf2.PageCode
			and uuf1.DataType = uuf2.DataType
			AND UUF1.DataSource = uuf2.DataSource
			-- 由于KPI定义中，
			-- 需要确保页面重复访问并非在同一次访问中发生的，
			-- 因此需要确保H5AccessId不一致
			and uuf1.H5AccessId != uuf2.H5AccessId 
				then 1 
				else null end
			) PageHistoryAccessTimes
	-- 窗口期内相同页面被重复访问的次数
	from STG.DBM_DLAUNCH_VISIT_RECORD uuf1 -- 分组表
		left join ( SELECT BDVR1.ActivityID , BDVR1.UserID, BDVR1.H5AccessID,
			case when BDVR1.DataSource = 'DBM' 
					THEN EDAAQ1.DataType ELSE BDVR1.DataType END DataType,
			BDVR1.DataSource,
			case when BDVR1.DataSource = 'DBM'
					THEN EDAAQ1.PageCode ELSE BDVR1.PageCode END PageCode,
			BDVR1.CreateDate
		FROM STG.DBM_DLAUNCH_VISIT_RECORD BDVR1
			LEFT JOIN
			(SELECT AAA.ActivityID, AAA.DataType, AAA.DataSource, AAA.PageCode
			FROM ODS.EXCEL_DLAUNCH_PAGE_CODE_FILTER AAA
			WHERE TAG = 1 ) EDAAQ1
			ON BDVR1.ActivityID = EDAAQ1.ActivityID
				and BDVR1.DataType = EDAAQ1.DataType
				and BDVR1.DataSource = EDAAQ1.DataSource
				and BDVR1.PageCode = EDAAQ1.PageCode
			) uuf2 -- 计数表
		-- 建立以活动，用户，数据源为基准，在不同时间，
		-- 不同页面数的笛卡尔积，并且对其进行分组去重计数
		-- 从而获取在某一时间段之前，用户全部访问的页面数量情况
		on 			uuf1.ActivityID = uuf2.ActivityID
			and uuf1.UserID = uuf2.UserID
			AND uuf1.DataSource = uuf2.DataSource
	where 		uuf1.CreateDate >= uuf2.CreateDate
	-- 仅保留比目标是阶段小的数据
	group by 	uuf1.ActivityID, uuf1.UserID, uuf1.H5AccessID, uuf1.DataType, uuf1.DataSource,
			uuf1.PageCode, uuf1.CreateDate;
GO
-- =============================================
-- Author:		<Jec Wang>
-- Create date: <2021-10-14>
-- Description:	<TABLEAU DLAUNCH DASHBOARD>
-- 				用户和医院信息
-- =============================================
CREATE PROCEDURE DW.SP_DIM_DLAUNCH_LIST
	(@sourceCount INT OUTPUT,
	@deleteCount INT OUTPUT,
	@insertCount INT OUTPUT,
	@updateCount INT OUTPUT)
AS
BEGIN
	SELECT @sourceCount = COUNT(1)
	FROM DW.DIM_DLAUNCH_LIST;
	SET XACT_ABORT on;
	BEGIN TRAN myTran;
	TRUNCATE TABLE  DW.DIM_DLAUNCH_LIST
	INSERT INTO  
				DW.DIM_DLAUNCH_LIST
		(
		ActivityName,ActivityID,ActivityType,ActivityStatus,ActivityTTLUnquiePges,DataSource,StartTime,ProductID,ProductName)
	SELECT ActivityName, ActivityID, ActivityType, ActivityStatus, ActivityTTLUnquiePges, DataSource, StartTime, ProductID, ProductName
	FROM ODS.EXCEL_DLAUNCH_LIST
	COMMIT TRAN myTran;
	SET @insertCount = @@ROWCOUNT;
END;

GO
-- =============================================
-- Author:		<Jec Wang>
-- Create date: <2021-10-14>
-- Description:	<TABLEAU DLAUNCH DASHBOARD>
-- 				用户和医院信息
-- =============================================
CREATE PROCEDURE DW.SP_DIM_DLAUNCH_USER_INFO
	(@sourceCount INT OUTPUT,
	@deleteCount INT OUTPUT,
	@insertCount INT OUTPUT,
	@updateCount INT OUTPUT)
AS
BEGIN
	SELECT @sourceCount = COUNT(1)
	FROM DW.DIM_DLAUNCH_USER_INFO;
	SET XACT_ABORT on;
	BEGIN TRAN myTran;
	TRUNCATE TABLE  DW.DIM_DLAUNCH_USER_INFO;
	INSERT INTO  
				DW.DIM_DLAUNCH_USER_INFO
		(
		UserId,UserName,Type,Gender,Department,Profession,HospitalName,Province,City,delete_status,IsBboost,HospitalLevel)
	SELECT UserId, UserName, Type, Gender, Department, Profession, HospitalName, Province, City, delete_status, IsBboost, HospitalLevel
	FROM DW.VW_T_DIM_DLAUNCH_USER_INFO;
	COMMIT TRAN myTran;
	SET @insertCount = @@ROWCOUNT;
END;

GO
-- =============================================
-- Author:		<Jec Wang>
-- Create date: <2021-10-14>
-- Description:	<TABLEAU DLAUNCH DASHBOARD>
-- 				用户访问日志汇总
-- =============================================
CREATE PROCEDURE DW.SP_FACT_DLAUNCH_VISIT_RECORD
	(@sourceCount INT OUTPUT,
	@deleteCount INT OUTPUT,
	@insertCount INT OUTPUT,
	@updateCount INT OUTPUT)
AS
BEGIN
	SELECT @sourceCount = COUNT(1)
	FROM DW.FACT_DLAUNCH_VISIT_RECORD;
	SET XACT_ABORT on;
	BEGIN TRAN myTran;
	TRUNCATE TABLE  DW.FACT_DLAUNCH_VISIT_RECORD
	INSERT INTO  
			DW.FACT_DLAUNCH_VISIT_RECORD
		(
		ActivityID,ActivityName,UserId,UserName,H5AccessId,PageCode,DataType,TimeSpand,LoginDate,CreateDate,DataSource )
	SELECT ActivityID, ActivityName, UserId, UserName, H5AccessId, PageCode, DataType, TimeSpand, LoginDate, CreateDate, DataSource
	FROM STG.DBM_DLAUNCH_VISIT_RECORD
	COMMIT TRAN myTran;
	SET @insertCount = @@ROWCOUNT;
END;
GO
-- =============================================
-- Author:		<Jec Wang>
-- Create date: <2021-10-14>
-- Description:	<TABLEAU DLAUNCH DASHBOARD>
-- 				用户动态最大访问页面数与回看情况
-- =============================================
CREATE PROCEDURE DW.SP_FACT_DLAUNCH_VISIT_SUMMARY
	(@sourceCount INT OUTPUT,
	@deleteCount INT OUTPUT,
	@insertCount INT OUTPUT,
	@updateCount INT OUTPUT)
AS
BEGIN
	SELECT @sourceCount = COUNT(1)
	FROM DW.FACT_DLAUNCH_VISIT_RECORD;
	SET XACT_ABORT on;
	BEGIN TRAN myTran;
	TRUNCATE TABLE  DW.FACT_DLAUNCH_VISIT_SUMMARY
	INSERT INTO  
			DW.FACT_DLAUNCH_VISIT_SUMMARY
		(
		ActivityID,UserID,H5AccessID,DataType,PageCode,CreateDate,MaxAccessedPageCode,PageHistoryAccessTimes,DataSource)
	SELECT ActivityID, UserID, H5AccessID, DataType, PageCode, CreateDate, MaxAccessedPageCode, PageHistoryAccessTimes, DataSource
	FROM DW.VW_T_FACT_DBM_DLAUNCH_VISIT_SUMMARY;
	COMMIT TRAN myTran;
	SET @insertCount = @@ROWCOUNT;
END;
GO

GO
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DW_SP_INFO',@columnName=N'', @comment=N'nan';
GO
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DW_SP_INFO',@columnName=N'OBJECTID', @comment=N'primary key IDENTITY';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DW_SP_INFO',@columnName=N'PROCEDURENAME', @comment=N'存储过程名称';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DW_SP_INFO',@columnName=N'SOURCETABLEVIEW', @comment=N'存储过程中所调用的表名';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DW_SP_INFO',@columnName=N'TARGETTABLE', @comment=N'目标表 如果目标表为多表则<,>隔开';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DW_SP_INFO',@columnName=N'OPERATION', @comment=N'固定词 INSERT / DELETE / TRUNCATE';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DW_SP_INFO',@columnName=N'ISDELETED', @comment=N'软删除 0.未删除 1.删除';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DW_SP_INFO',@columnName=N'Project', @comment=N'创建人|项目名';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DW_SP_INFO',@columnName=N'ETL_TIME', @comment=N'入库时间';
GO
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'DIM_DLAUNCH_LIST',@columnName=N'', @comment=N'DLAUNCH活动情况手工表 ods.EXCEL_DLAUNCH_LIST';
GO
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'DIM_DLAUNCH_LIST',@columnName=N'ACTIVITYID', @comment=N'活动ID';
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'DIM_DLAUNCH_LIST',@columnName=N'STARTTIME', @comment=N'开始时间';
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'DIM_DLAUNCH_LIST',@columnName=N'PRODUCTNAME', @comment=N'产品名称';
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'DIM_DLAUNCH_LIST',@columnName=N'PRODUCTID', @comment=N'产品ID';
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'DIM_DLAUNCH_LIST',@columnName=N'ACTIVITYNAME', @comment=N'活动名称';
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'DIM_DLAUNCH_LIST',@columnName=N'ACTIVITYTYPE', @comment=N'活动类型';
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'DIM_DLAUNCH_LIST',@columnName=N'ACTIVITYTTLUNQUIEPGES', @comment=N'活动最大不同页面数';
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'DIM_DLAUNCH_LIST',@columnName=N'ACTIVITYSTATUS', @comment=N'活动状态';
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'DIM_DLAUNCH_LIST',@columnName=N'DATASOURCE', @comment=N'数据来源 DBM数据库|MANUAL手工导入';

GO
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'DIM_DLAUNCH_USER_INFO',@columnName=N'', @comment=N'医生信息表 ODS.DBM_SYS_HOSPITAL +  STG.DBM_MIN_USER';
GO
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'DIM_DLAUNCH_USER_INFO',@columnName=N'CITY', @comment=N'市';
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'DIM_DLAUNCH_USER_INFO',@columnName=N'USERID', @comment=N'用户主键';
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'DIM_DLAUNCH_USER_INFO',@columnName=N'DEPARTMENT', @comment=N'科室信息';
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'DIM_DLAUNCH_USER_INFO',@columnName=N'GENDER', @comment=N'性别 男|女|其他（未知）';
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'DIM_DLAUNCH_USER_INFO',@columnName=N'HOSPITALLEVEL', @comment=N'医院等级 三级甲等至一级丙等|另含有其他';
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'DIM_DLAUNCH_USER_INFO',@columnName=N'HOSPITALNAME', @comment=N'医院名称';
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'DIM_DLAUNCH_USER_INFO',@columnName=N'ISBBOOST', @comment=N'是否为BOOST用户';
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'DIM_DLAUNCH_USER_INFO',@columnName=N'PROFESSION', @comment=N'职称';
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'DIM_DLAUNCH_USER_INFO',@columnName=N'PROVINCE', @comment=N'省';
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'DIM_DLAUNCH_USER_INFO',@columnName=N'TYPE', @comment=N'用户类型 医生|护士|药师|医管|医技|医工';
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'DIM_DLAUNCH_USER_INFO',@columnName=N'DELETE_STATUS', @comment=N'软删除情况 1使用中2删除';
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'DIM_DLAUNCH_USER_INFO',@columnName=N'USERNAME', @comment=N'姓名';

GO
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'FACT_DLAUNCH_VISIT_RECORD',@columnName=N'', @comment=N'页面访问日志汇总 STG.DBM_DLAUNCH_VISIT_RECORD';
GO
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'FACT_DLAUNCH_VISIT_RECORD',@columnName=N'ACTIVITYNAME', @comment=N'活动名称';
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'FACT_DLAUNCH_VISIT_RECORD',@columnName=N'ACTIVITYID', @comment=N'活动ID';
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'FACT_DLAUNCH_VISIT_RECORD',@columnName=N'USERID', @comment=N'用户主键';
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'FACT_DLAUNCH_VISIT_RECORD',@columnName=N'TIMESPAND', @comment=N'在线时长';
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'FACT_DLAUNCH_VISIT_RECORD',@columnName=N'PAGECODE', @comment=N'页面CODE';
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'FACT_DLAUNCH_VISIT_RECORD',@columnName=N'USERNAME', @comment=N'姓名';
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'FACT_DLAUNCH_VISIT_RECORD',@columnName=N'H5ACCESSID', @comment=N'H5访问ID';
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'FACT_DLAUNCH_VISIT_RECORD',@columnName=N'DATATYPE', @comment=N'数据来源 ACCESS|ANSWER|PAGE|VIDEO';
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'FACT_DLAUNCH_VISIT_RECORD',@columnName=N'DATASOURCE', @comment=N'数据来源 DBM数据库|MANUAL手工导入';
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'FACT_DLAUNCH_VISIT_RECORD',@columnName=N'CREATEDATE', @comment=N'创建时间 yyyy-mm-dd HH:MM:SS';
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'FACT_DLAUNCH_VISIT_RECORD',@columnName=N'LOGINDATE', @comment=N'登录时间日期 yyyy-mm-dd HH:MM:SS';

GO
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'FACT_DLAUNCH_VISIT_SUMMARY',@columnName=N'', @comment=N'页面访问频率与最大访问页面数 DW.FACT_DLAUNCH_VISIT_RECORD';
GO
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'FACT_DLAUNCH_VISIT_SUMMARY',@columnName=N'ACTIVITYID', @comment=N'活动ID';
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'FACT_DLAUNCH_VISIT_SUMMARY',@columnName=N'CREATEDATE', @comment=N'创建时间 yyyy-mm-dd HH:MM:SS';
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'FACT_DLAUNCH_VISIT_SUMMARY',@columnName=N'DATASOURCE', @comment=N'数据来源 DBM数据库|MANUAL手工导入';
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'FACT_DLAUNCH_VISIT_SUMMARY',@columnName=N'DATATYPE', @comment=N'数据来源 ACCESS|ANSWER|PAGE|VIDEO';
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'FACT_DLAUNCH_VISIT_SUMMARY',@columnName=N'H5ACCESSID', @comment=N'H5访问ID';
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'FACT_DLAUNCH_VISIT_SUMMARY',@columnName=N'MAXACCESSEDPAGECODE', @comment=N'最大访问不同页面数(每用户）';
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'FACT_DLAUNCH_VISIT_SUMMARY',@columnName=N'PAGECODE', @comment=N'页面CODE';
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'FACT_DLAUNCH_VISIT_SUMMARY',@columnName=N'PAGEHISTORYACCESSTIMES', @comment=N'页面历史访问次数(用户）';
EXEC ODS.SP_AddOrUpdateComment @schema=N'DW', @tableName=N'FACT_DLAUNCH_VISIT_SUMMARY',@columnName=N'USERID', @comment=N'用户主键';
GO
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_ACCESS_RECORD',@columnName=N'', @comment=N'DLAUNCH页面数据';
GO
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_ACCESS_RECORD',@columnName=N'ACTIVITY_ID', @comment=N'活动ID';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_ACCESS_RECORD',@columnName=N'USER_NAME', @comment=N'姓名';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_ACCESS_RECORD',@columnName=N'USER_ID', @comment=N'用户主键';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_ACCESS_RECORD',@columnName=N'PRODUCT_NAME', @comment=N'产品名称';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_ACCESS_RECORD',@columnName=N'PRODUCT_ID', @comment=N'产品ID';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_ACCESS_RECORD',@columnName=N'PAGE_CODE', @comment=N'页面CODE';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_ACCESS_RECORD',@columnName=N'PAGE_ACCESS_ID', @comment=N'页面访问ID';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_ACCESS_RECORD',@columnName=N'LOGOUT_TIME', @comment=N'页面退出时间';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_ACCESS_RECORD',@columnName=N'ACTIVITY_NAME', @comment=N'活动名称';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_ACCESS_RECORD',@columnName=N'LOGIN_TIME', @comment=N'登录时间 时分秒';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_ACCESS_RECORD',@columnName=N'LOGIN_DATA', @comment=N'登录日期 年月日';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_ACCESS_RECORD',@columnName=N'ID', @comment=N'访问主键';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_ACCESS_RECORD',@columnName=N'H5_ACCESS_ID', @comment=N'H5访问ID';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_ACCESS_RECORD',@columnName=N'ETL_TIME', @comment=N'数据提取时间';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_ACCESS_RECORD',@columnName=N'CREATE_TIME', @comment=N'创建时间';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_ACCESS_RECORD',@columnName=N'LOGOUT_DATA', @comment=N'页面退出日期';

GO
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_ANSWER_RECORD',@columnName=N'', @comment=N'DLAUNCH问卷答题数据';
GO
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_ANSWER_RECORD',@columnName=N'USER_NAME', @comment=N'姓名';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_ANSWER_RECORD',@columnName=N'USER_ID', @comment=N'用户主键';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_ANSWER_RECORD',@columnName=N'QUESTION_TYPE', @comment=N'问题题型 1、单选 2、多选 3、问答';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_ANSWER_RECORD',@columnName=N'QUESTION_CODE', @comment=N'问题编号';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_ANSWER_RECORD',@columnName=N'PRODUCT_NAME', @comment=N'产品名称';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_ANSWER_RECORD',@columnName=N'PRODUCT_ID', @comment=N'产品ID';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_ANSWER_RECORD',@columnName=N'QUESTION_CATEGORY', @comment=N'问题属性：1、序章问题 2、调研问题';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_ANSWER_RECORD',@columnName=N'H5_ACCESS_ID', @comment=N'H5访问ID';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_ANSWER_RECORD',@columnName=N'ETL_TIME', @comment=N'数据提取时间';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_ANSWER_RECORD',@columnName=N'CREATE_TIME', @comment=N'创建时间';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_ANSWER_RECORD',@columnName=N'ANSWER_RECORD', @comment=N'答案记录';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_ANSWER_RECORD',@columnName=N'ACTIVITY_NAME', @comment=N'活动名称';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_ANSWER_RECORD',@columnName=N'ACTIVITY_ID', @comment=N'活动ID';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_ANSWER_RECORD',@columnName=N'ID', @comment=N'访问主键';

GO
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_PAGE_RECORD',@columnName=N'', @comment=N'DLAUNCH访问页面数据';
GO
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_PAGE_RECORD',@columnName=N'STATUS', @comment=N'学习状态 1学习中 2已完成';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_PAGE_RECORD',@columnName=N'PRODUCT_NAME', @comment=N'产品名称';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_PAGE_RECORD',@columnName=N'PRODUCT_ID', @comment=N'产品ID';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_PAGE_RECORD',@columnName=N'PAGE_CODE', @comment=N'页面CODE';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_PAGE_RECORD',@columnName=N'USER_ID', @comment=N'用户主键';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_PAGE_RECORD',@columnName=N'H5_ACCESS_ID', @comment=N'H5访问ID';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_PAGE_RECORD',@columnName=N'ETL_TIME', @comment=N'数据提取时间';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_PAGE_RECORD',@columnName=N'ACTIVITY_NAME', @comment=N'活动名称';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_PAGE_RECORD',@columnName=N'ACTIVITY_ID', @comment=N'活动ID';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_PAGE_RECORD',@columnName=N'ID', @comment=N'访问主键';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_PAGE_RECORD',@columnName=N'CREATE_TIME', @comment=N'创建时间';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_PAGE_RECORD',@columnName=N'USER_NAME', @comment=N'姓名';

GO
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_VIDEO_RECORD',@columnName=N'', @comment=N'DLAUNCH观看视频数据';
GO
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_VIDEO_RECORD',@columnName=N'ACTIVITY_NAME', @comment=N'活动名称';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_VIDEO_RECORD',@columnName=N'ACTIVITY_ID', @comment=N'活动ID';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_VIDEO_RECORD',@columnName=N'VIEW_LENGTH', @comment=N'视频观看时长';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_VIDEO_RECORD',@columnName=N'VIDEO_CODE', @comment=N'视频CODE';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_VIDEO_RECORD',@columnName=N'USER_NAME', @comment=N'姓名';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_VIDEO_RECORD',@columnName=N'PRODUCT_NAME', @comment=N'产品名称';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_VIDEO_RECORD',@columnName=N'USER_ID', @comment=N'用户主键';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_VIDEO_RECORD',@columnName=N'PAGE_ACCESS_ID', @comment=N'页面访问ID';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_VIDEO_RECORD',@columnName=N'ID', @comment=N'访问主键';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_VIDEO_RECORD',@columnName=N'H5_ACCESS_ID', @comment=N'H5访问ID';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_VIDEO_RECORD',@columnName=N'ETL_TIME', @comment=N'数据提取时间';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_VIDEO_RECORD',@columnName=N'CREATE_TIME', @comment=N'创建时间';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'DBM_ACT_DLAUNCH_VIDEO_RECORD',@columnName=N'PRODUCT_ID', @comment=N'产品ID';

GO
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'EXCEL_DLAUNCH_LIST',@columnName=N'', @comment=N'DLAUNCH活动情况手工表';
GO
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'EXCEL_DLAUNCH_LIST',@columnName=N'ETL_TIME', @comment=N'数据提取时间';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'EXCEL_DLAUNCH_LIST',@columnName=N'STARTTIME', @comment=N'活动开始时间';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'EXCEL_DLAUNCH_LIST',@columnName=N'PRODUCTNAME', @comment=N'平台名称 围术期|肾科|重症';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'EXCEL_DLAUNCH_LIST',@columnName=N'DATASOURCE', @comment=N'数据来源 DBM数据库|MANUAL手工导入';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'EXCEL_DLAUNCH_LIST',@columnName=N'PRODUCTID', @comment=N'平台主键';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'EXCEL_DLAUNCH_LIST',@columnName=N'ACTIVITYTTLUNQUIEPGES', @comment=N'活动最大不同页面数';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'EXCEL_DLAUNCH_LIST',@columnName=N'ACTIVITYSTATUS', @comment=N'活动状态';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'EXCEL_DLAUNCH_LIST',@columnName=N'ACTIVITYNAME', @comment=N'活动名称';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'EXCEL_DLAUNCH_LIST',@columnName=N'ACTIVITYID', @comment=N'活动主键';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'EXCEL_DLAUNCH_LIST',@columnName=N'ACTIVITYTYPE', @comment=N'活动类型';

GO
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'EXCEL_DLAUNCH_PAGE_CODE_FILTER',@columnName=N'', @comment=N'DLAUNCH活动页面统计方式';
GO
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'EXCEL_DLAUNCH_PAGE_CODE_FILTER',@columnName=N'ACTIVITYID', @comment=N'活动主键';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'EXCEL_DLAUNCH_PAGE_CODE_FILTER',@columnName=N'DATASOURCE', @comment=N'数据来源 DBM数据库|MANUAL手工导入';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'EXCEL_DLAUNCH_PAGE_CODE_FILTER',@columnName=N'DataType', @comment=N'数据来源 ACCESS|ANSWER|PAGE|VIDEO';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'EXCEL_DLAUNCH_PAGE_CODE_FILTER',@columnName=N'PAGECODE', @comment=N'页面CODE';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'EXCEL_DLAUNCH_PAGE_CODE_FILTER',@columnName=N'TAG', @comment=N'页面是否纳入统计标签 1统计0删除';

GO
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'EXCEL_DLAUNCH_RPM_MANUAL_INPUT',@columnName=N'', @comment=N'DLAUNCH RPM1|RPM2手工数据';
GO
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'EXCEL_DLAUNCH_RPM_MANUAL_INPUT',@columnName=N'USERID', @comment=N'用户主键';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'EXCEL_DLAUNCH_RPM_MANUAL_INPUT',@columnName=N'TIMESPAND', @comment=N'在线时长';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'EXCEL_DLAUNCH_RPM_MANUAL_INPUT',@columnName=N'PAGECODE', @comment=N'页面CODE';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'EXCEL_DLAUNCH_RPM_MANUAL_INPUT',@columnName=N'LOGINDATE', @comment=N'登录时间日期 yyyy-mm-dd HH:MM:SS';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'EXCEL_DLAUNCH_RPM_MANUAL_INPUT',@columnName=N'H5ACCESSID', @comment=N'访问主键';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'EXCEL_DLAUNCH_RPM_MANUAL_INPUT',@columnName=N'ACTIVITYNAME', @comment=N'活动名称';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'EXCEL_DLAUNCH_RPM_MANUAL_INPUT',@columnName=N'DataType', @comment=N'数据来源 ACCESS|ANSWER|PAGE|VIDEO';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'EXCEL_DLAUNCH_RPM_MANUAL_INPUT',@columnName=N'CREATEDATE', @comment=N'创建时间';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'EXCEL_DLAUNCH_RPM_MANUAL_INPUT',@columnName=N'ANSWER', @comment=N'答案记录';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'EXCEL_DLAUNCH_RPM_MANUAL_INPUT',@columnName=N'ACTIVITYID', @comment=N'活动主键';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'EXCEL_DLAUNCH_RPM_MANUAL_INPUT',@columnName=N'ETL_TIME', @comment=N'数据提取时间';
EXEC ODS.SP_AddOrUpdateComment @schema=N'ODS', @tableName=N'EXCEL_DLAUNCH_RPM_MANUAL_INPUT',@columnName=N'USERNAME', @comment=N'姓名';

EXEC ODS.SP_AddOrUpdateComment @schema=N'STG', @tableName=N'DBM_DLAUNCH_VISIT_RECORD',@columnName=N'', @comment=N'页面访问日志汇总 ACCESS|VIDEO|PAGE|ANSWER|RPM';
EXEC ODS.SP_AddOrUpdateComment @schema=N'STG', @tableName=N'DBM_DLAUNCH_VISIT_RECORD',@columnName=N'ACTIVITYID', @comment=N'活动ID';
EXEC ODS.SP_AddOrUpdateComment @schema=N'STG', @tableName=N'DBM_DLAUNCH_VISIT_RECORD',@columnName=N'ACTIVITYNAME', @comment=N'活动名称';
EXEC ODS.SP_AddOrUpdateComment @schema=N'STG', @tableName=N'DBM_DLAUNCH_VISIT_RECORD',@columnName=N'USERID', @comment=N'用户主键';
EXEC ODS.SP_AddOrUpdateComment @schema=N'STG', @tableName=N'DBM_DLAUNCH_VISIT_RECORD',@columnName=N'USERNAME', @comment=N'姓名';
EXEC ODS.SP_AddOrUpdateComment @schema=N'STG', @tableName=N'DBM_DLAUNCH_VISIT_RECORD',@columnName=N'PAGECODE', @comment=N'页面CODE';
EXEC ODS.SP_AddOrUpdateComment @schema=N'STG', @tableName=N'DBM_DLAUNCH_VISIT_RECORD',@columnName=N'H5ACCESSID', @comment=N'H5访问ID';
EXEC ODS.SP_AddOrUpdateComment @schema=N'STG', @tableName=N'DBM_DLAUNCH_VISIT_RECORD',@columnName=N'DATATYPE', @comment=N'数据来源 ACCESS|ANSWER|PAGE|VIDEO';
EXEC ODS.SP_AddOrUpdateComment @schema=N'STG', @tableName=N'DBM_DLAUNCH_VISIT_RECORD',@columnName=N'TIMESPAND', @comment=N'在线时长';
EXEC ODS.SP_AddOrUpdateComment @schema=N'STG', @tableName=N'DBM_DLAUNCH_VISIT_RECORD',@columnName=N'LOGINDATE', @comment=N'登录时间日期 yyyy-mm-dd HH:MM:SS';
EXEC ODS.SP_AddOrUpdateComment @schema=N'STG', @tableName=N'DBM_DLAUNCH_VISIT_RECORD',@columnName=N'CREATEDATE', @comment=N'创建时间 yyyy-mm-dd HH:MM:SS';
EXEC ODS.SP_AddOrUpdateComment @schema=N'STG', @tableName=N'DBM_DLAUNCH_VISIT_RECORD',@columnName=N'DATASOURCE', @comment=N'数据来源 DBM数据库|MANUAL手工导入';
GO
EXEC ODS.SP_DW_SP_LOG 'ODS.SP_DBM_ACT_DLAUNCH_ACCESS_RECORD';
EXEC ODS.SP_DW_SP_LOG 'ODS.SP_DBM_ACT_DLAUNCH_ANSWER_RECORD';
EXEC ODS.SP_DW_SP_LOG 'ODS.SP_DBM_ACT_DLAUNCH_PAGE_RECORD';
EXEC ODS.SP_DW_SP_LOG 'ODS.SP_DBM_ACT_DLAUNCH_VIDEO_RECORD';
-- !DW_C_DLaunch_2021221 
EXEC ODS.SP_DW_SP_LOG 'ODS.SP_DBM_ACT_DLAUNCH_GAME_RECORD';
EXEC ODS.SP_DW_SP_LOG 'ODS.SP_DBM_ACT_DLAUNCH_GAME_RECORD_INITIALIZE';
-- !DW_C_DLaunch_2021221 
EXEC ODS.SP_DW_SP_LOG 'ODS.SP_DBM_ACT_DLAUNCH_ACCESS_RECORD_INITIALIZE';
EXEC ODS.SP_DW_SP_LOG 'ODS.SP_DBM_ACT_DLAUNCH_ANSWER_RECORD_INITIALIZE';
EXEC ODS.SP_DW_SP_LOG 'ODS.SP_DBM_ACT_DLAUNCH_PAGE_RECORD_INITIALIZE';
EXEC ODS.SP_DW_SP_LOG 'ODS.SP_DBM_ACT_DLAUNCH_VIDEO_RECORD_INITIALIZE';
EXEC ODS.SP_DW_SP_LOG 'STG.SP_DBM_DLAUNCH_VISIT_RECORD';
EXEC ODS.SP_DW_SP_LOG 'DW.SP_DIM_DLAUNCH_LIST';
EXEC ODS.SP_DW_SP_LOG 'DW.SP_DIM_DLAUNCH_USER_INFO';
EXEC ODS.SP_DW_SP_LOG 'DW.SP_FACT_DLAUNCH_VISIT_RECORD';
EXEC ODS.SP_DW_SP_LOG 'DW.SP_FACT_DLAUNCH_VISIT_SUMMARY';
GO